package com.academic_system.mapper;

import com.academic_system.model.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @description studentDAO
 * @project_name academic_system
 * @package com.academic_system.dao
 **/
@Repository
public interface StudentDao {
    /**
     * @param stu_num
     * @Description 根据Student表中的学生学号查询学生个人信息
     * @methodName queryStudentByStuNum
     * @returnType Student
     **/
    @Select("select s.stu_info_id,s.stu_num,s.stu_name,s.stu_sex,s.stu_idcard_num,s.stu_birthday,s.stu_nation,s.stu_poli,s.stu_phone,s.stu_nativeplace,s.stu_password,s.stu_grade," +
            "c.cet_4_grade,c.cet_6_grade," +
            "a.academy_name" +
            " from student s,academy a,cet_46 c" +
            " where s.stu_num=#{stu_num} and s.academy_id=a.academy_id and s.cet_46_id=c.cet_46_id")
    StudentInfo queryStudentByStuNum(@Param("stu_num") int stu_num);

    /**
     * @param stu_num
     * @Description 查询学号是否存在
     * @methodName queryStuNum
     * @returnType int
     **/
    @Select("select * from student where stu_num=#{stu_num}")
    Student queryStuNum(@Param("stu_num") int stu_num);

    /**
     * @param student
     * @Description 增加一个学生（学号+密码）
     * @methodName addStudent
     * @returnType boolean
     **/
    @Insert("insert into student(stu_num,stu_password) values(#{student.stuNum},#{student.stuPassword})")
    boolean addStudent(@Param("student") Student student, @Param("id") int id);

    /**
     * @param student
     * @Description 修改学生信息表（姓名，性别，身份证，出生年月，民族，电话，籍贯）
     * @methodName updateStudent
     * @returnType boolean
     **/
    @Update("update student set stu_name=#{student.stuName},stu_sex=#{student.stuSex},stu_idcard_num=#{student.stuIdcardNum},stu_birthday=#{student.stuBirthday},stu_nation=#{student.stuNation},stu_phone=#{student.stuPhone},stu_nativeplace=#{student.stuNativeplace} where stu_num=#{student.stuNum}")
    boolean updateStudent(@Param("student") Student student);

    /**
     * @param stu_num
     * @Description 删除学生信息表【警告！这是真删！无特殊情况不可调用此接口！】
     * @methodName deleteStudent
     * @returnType boolean
     **/
    @Delete("delete from student where stu_num=#{stu_num}")
    boolean deleteStudent(@Param("stu_num") int stu_num);

    /**
     * @Description 查询全部学生
     * @methodName queryStudent
     * @returnType List<Student>
     **/
    @Select("select * from student")
    List<Student> queryStudent();

    /**
     * @Description 查询全部年级（除去重复字段）
     * @methodName queryGrade
     * @returnType List
     **/
    @Select("select distinct stu_grade from student order by stu_grade desc")
    List<String> queryGrade();

    /**
     * @param
     * @Description 跟据学号查学生id
     * @methodName
     * @returnType
     **/
    @Select("select stu_info_id from student where stu_num=#{stuNum}")
    Student queryStuIdByStuNum(@Param("stuNum") int stuNum);

    /**
     * @param
     * @Description 根据学生id查找学生信息
     * @methodName
     * @returnType
     **/
    @Select("select * from student where stu_info_id=#{stuInfoId}")
    Student queryStudentByStuId(@Param("stuInfoId") int stuInfoId);

    /**
     * @param
     * @Description 查找所有学生id， 学号，姓名
     * @methodName
     * @returnType
     **/
    @Select("select stu_info_id,stu_num,stu_name from student")
    List<MarkCondition> queryAllStudent();

    @Select("select count(te_info_id) from teacher where te_idcard_num = #{name} and te_password = #{password}")
    int queryTeacherByNumAndPwd(@Param("name")String name, @Param("password")String password);

    @Select("select stu_info_id stuInfoId from student where stu_Num = #{name} and stu_password = #{password}")
    Student queryDisNameAndPwd(@Param("name")String name, @Param("password")String password);

    @Insert("insert into project values(NULL,#{termId},#{stuInfoId},#{projectName},#{projectContent},#{projectPhonenum},#{projectStartDate},#{projectEndtDate},#{projectContactPeople},#{projectPlatform},1)")
    int insertProject(Project project);

    @Select("select project_id projectId,term_id termId,stu_info_id stuInfoId,project_name projectName,project_content projectContent,project_phonenum projectPhonenum,project_start_date projectStartDate,project_endt_date projectEndtDate,project_contact_people projectContactPeople,project_platform projectPlatform,project_state projectState from project")
    List<Project> findAllProject();

    @Select("SELECT p.project_id,stu.stu_num,stu.stu_name,stu.stu_sex,a.academy_name,stu.stu_grade,t.term_num,p.project_name,p.project_content,p.project_start_date,p.project_endt_date,p.project_contact_people,p.project_phonenum,p.project_platform,p.project_state FROM `project` p,student stu,term t,academy a\r\n" +
            "where a.academy_id = stu.academy_id and p.term_id = t.term_id and stu.stu_info_id = p.stu_info_id")
    List<StuProject> findAllStuProject();

    @Select("SELECT p.project_id,stu.stu_num,stu.stu_name,stu.stu_sex,a.academy_name,stu.stu_grade,t.term_num,p.project_name,p.project_content,p.project_start_date,p.project_endt_date,p.project_contact_people,p.project_phonenum,p.project_platform,p.project_state FROM `project` p,student stu,term t,academy a where a.academy_id = stu.academy_id and p.term_id = t.term_id and stu.stu_info_id = p.stu_info_id and stu.stu_info_id = #{stuId}")
    List<StuProject> findMyProject(@Param("stuId")int stuId);

    @Update("update project set project_name = #{projectName},project_content=#{projectContent},"
            + "project_phonenum=#{projectPhonenum},project_start_date=#{projectStartDate},"
            + "project_endt_date=#{projectEndtDate},project_contact_people=#{projectContactPeople},"
            + "project_platform = #{projectPlatform},project_state=#{projectState} where project_id = #{projectId}")
    boolean changeMyProject(StuProject stuProject);

    @Delete("delete from project where project_id = #{projectId}")
    boolean removeMyProject(StuProject stuProject);

    @Select("select count(stu_info_id) from student where stu_num = #{name} and stu_password = #{password}")
    int queryStudentByNumAndPwd(@Param("name")String name, @Param("password")String password);
}

